﻿
////////////////////////////////////////////////////////////////////////////////////////////
// 文件名: ETLTaskDAL.gen.cs
//
// 模块名称: ETLTaskDal
//
// 作者: yqf
//
// 历史记录（记录修改记录，修改文件后请添加修改记录，注明修改时间、修改人，修改内容）:
// 13-2-19    yqf    创建文件
////////////////////////////////////////////////////////////////////////////////////////////

using System;
using System.Data;
using System.Linq;
using System.Collections.Generic;
using System.Data.SqlClient;
using Common;
using Model;
using Oracle.DataAccess.Client;
using System.Threading;
using System.Collections;
using System.Text;

namespace DAL
{
    /// <summary>
    /// SysUser表数据访问层
    /// </summary>
    public class ETLTaskDAL : DB
    {
        public ETLTaskDAL(LoginInfo info)
        {
            base.loginInfo = info;
        }
        /// <summary>
        /// 根据TaskID字段获取ETL_TaskInfo实体对象
        /// </summary>
        /// <param name="taskID">TaskID</param>
        /// <returns>ETL_TaskInfo实体对象</returns>
        public ETL_TaskInfo GetInfoById(string taskID)
        {
            //添加输入输出参数
            SqlParameter[] sqlparams = { 
            new SqlParameter("@TaskID",taskID )
          };

            DataTable dt = new DataTable();
            HIDB.dbo con = base.conn();
            //执行命令返回DataTable对象
            dt = con.StoredDataTable("Working_db.UP_ETL_Task_Item", sqlparams);

            if (dt != null && dt.Rows.Count > 0)
            {
                ETL_TaskInfo model = new ETL_TaskInfo
                {
                    TaskID = dt.Rows[0]["TaskID"].ToString(),
                    TaskName = dt.Rows[0]["TaskName"].ToString(),
                    TaskType = int.Parse(dt.Rows[0]["TaskType"].ToString()),
                    FrequencyType = Convert.ToInt32(dt.Rows[0]["FrequencyType"]),
                    TaskStartTime = Convert.ToDateTime(dt.Rows[0]["TaskStartTime"]).ToShortTimeString(),
                    TaskEndTime = Convert.ToDateTime(dt.Rows[0]["TaskEndTime"]).ToShortTimeString(),
                    TaskIntervalDays = int.Parse(dt.Rows[0]["TaskIntervalDays"].ToString()),
                    TaskEveryDayIntervalMinute = int.Parse(dt.Rows[0]["TaskEveryDayIntervalMinute"].ToString()),
                    TaskStartDate = dt.Rows[0]["TaskStartDate"].ToString(),
                    TaskEndDate = dt.Rows[0]["TaskEndDate"].ToString(),//.ToString("yyyy-MM-dd"),
                    TaskStatus = int.Parse(dt.Rows[0]["TaskStatus"].ToString()),
                    UsedSMSReport = int.Parse(dt.Rows[0]["UsedSMSReport"].ToString()),
                    UsedMailReport = int.Parse(dt.Rows[0]["UsedMailReport"].ToString()),
                    UsedRTXReport = int.Parse(dt.Rows[0]["UsedRTXReport"].ToString()),
                    TaskDescription = dt.Rows[0]["TaskDescription"].ToString(),
                    CreateTime = dt.Rows[0]["CreateTime"].ToString()
                };
                return model;
            }

            return null;
        }
        /// <summary>
        /// 更新ETL_Job表记录
        /// </summary>
        /// <param name="ETL_JobInfo">ETL_JobInfo实体对象</param>
        public string Update(IDataParameter[] sqlparams)
        {
            try
            {
                HIDB.dbo con = base.conn();
                DataTable dt = con.StoredDataTable("Working_db.UP_ETL_Task_Update", sqlparams);
                if (dt != null && dt.Rows.Count > 0)
                {
                    return dt.Rows[0][0].ToString();
                }
                else
                    return "操作失败！";
            }
            catch (Exception ex) { throw ex; }
        }
        public void UpdateStep(IDataParameter[] sqlparams)
        {
            HIDB.dbo con = base.conn();
            con.StoredUpdate("Working_db.UP_ETL_Task_UpdateStep", sqlparams);
        }
        public bool CheckName(string name)
        {
            try
            {
                IDataParameter[] sqlparams = new SqlParameter[1];
                sqlparams[0] = new SqlParameter("@TaskName", name);
                HIDB.dbo con = base.conn();
                DataTable dt = con.StoredDataTable("Working_db.UP_ETL_Task_CheckName", sqlparams);
                if (dt != null && dt.Rows.Count > 0)
                {
                    return Convert.ToBoolean(Convert.ToInt32(dt.Rows[0][0].ToString()));
                }
                else
                    return false;
            }
            catch (Exception ex) { throw ex; }
        }
        public DataTable GetStep()
        {
            HIDB.dbo con = base.conn();
            return con.StoredDataTable("Working_db.UP_ETL_Task_GetStep");
        }
        public void DoTask(string taskID, string DBType)
        {
            try
            {
                IDataParameter[] paras = new SqlParameter[]{
                new SqlParameter("@TaskID", taskID),
                new SqlParameter("@DBType", DBType)
                };
                HIDB.dbo con = base.conn();
                con.ExecuteSql(@" 
exec Working_db.UP_ETL_Task_CopyTask @TaskID;
if(@DBType='2')
begin
exec working_db.CreateSysJob
exec [Working_db].[StartSysJob] @TaskID
end
", paras);
            }
            catch (Exception ex) { throw ex; }
        }

        //private  ManualResetEvent[] resetEvents;
        public void RunTask()
        {
            try
            {
                HIDB.dbo con = base.conn();
                DataTable dt = con.StoredDataTable("Working_db.UP_ETL_Task_RunTask");
                if (dt != null && dt.Rows.Count > 0)
                {
                    //resetEvents = new ManualResetEvent[dt.Rows.Count];
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        //resetEvents[i] = new ManualResetEvent(false);
                        //ThreadPool.QueueUserWorkItem(new WaitCallback(RunTask), (object)dt.Rows[i]);
                        try
                        {
                            Thread t = new Thread(new ParameterizedThreadStart(RunCallBackTask));
                            t.Start(dt.Rows[i]);
                        }
                        catch { }
                    }
                    //WaitHandle.WaitAll(resetEvents);
                }
            }
            catch (Exception ex)
            {
                if (ex.Message.IndexOf("正在中止线程") == -1)
                {
                    new SysOperationLog(loginInfo).Log("回调存储过程", "", "添加", OperationStatus.失败, ex.Message, "系统");
                }
            }
        }
        public void RunCallBackTask(object o)
        {
            DataRow dr = (DataRow)o;
            System.Text.StringBuilder log = new System.Text.StringBuilder();
            if (dr["CallBackType"].ToString() == "1")//存储过程回调方法
            {
                string dbinfo = string.Empty;
                string SysCode = dr["SysCode"].ToString();
                if (dr["OperationType"].ToString() == "E")
                    log.Append("源系统：");
                else
                    log.Append("对接系统：");
                log.Append(SysCode);
                try
                {
                    log.Append("<br/>CallBackName:");
                    log.Append(dr["CallBackName"].ToString());
                    log.Append("<br/>EtlID:");
                    log.Append(dr["EtlID"].ToString());
                    log.Append("<br/>GroupName:");
                    log.Append(dr["GroupName"].ToString());
                    int timeOut = 60;
                    if (dr["TaskEveryDayIntervalMinute"] != null)
                    {
                        if (dr["TaskEveryDayIntervalMinute"].ToString() == "0")
                            timeOut = 21600;
                        else
                            timeOut = Convert.ToInt32(dr["TaskEveryDayIntervalMinute"].ToString()) * 60;
                    }
                    if (timeOut > 21600)
                        timeOut = 21600;

                    EncrypCode ec = new EncrypCode();
                    if (dr["DBType"].ToString() == "1")
                    {
                        IDataParameter[] queryParas = new SqlParameter[2];
                        queryParas[0] = new SqlParameter("@P1", dr["EtlID"]);
                        queryParas[1] = new SqlParameter("@P2", dr["GroupName"]);
                        HIDB.dbo conn = HIDB.common.make(HIDB.DBType.SQLServer, ec.Decrypto(dr["DbConnectionStr"].ToString()));
                        conn.SqlTimeOut = timeOut;
                        try
                        {
                            conn.StoredUpdate(dr["CallBackName"].ToString(), queryParas);
                            //new SysOperationLog(loginInfo).Log("回调存储过程", "", "添加", OperationStatus.成功, log.ToString(), "系统");
                            // TaskDetailsID: null  5:回调存储过程 成功
                            new SysOperationLog(loginInfo).LogETL("", dr["EtlID"].ToString(), DateTime.Now, log.ToString(), 5, "系统");
                        }
                        catch (Exception ex)
                        {
                            log.Append("<br/>" + ex.Message);
                            //new SysOperationLog(loginInfo).Log("回调存储过程", "", "添加", OperationStatus.失败, log.ToString(), "系统");
                            // TaskDetailsID: null  6:回调存储过程 失败
                            new SysOperationLog(loginInfo).LogETL("", dr["EtlID"].ToString(), DateTime.Now, log.ToString(), 6, "系统");
                        }
                    }
                    else if (dr["DBType"].ToString() == "2")
                    {
                        OracleParameter[] queryParas = new OracleParameter[2];
                        queryParas[0] = new OracleParameter("@P1", dr["EtlID"]);
                        queryParas[1] = new OracleParameter("@P2", dr["GroupName"]);
                        HIDB.dbo conn = HIDB.common.make(HIDB.DBType.Oracle, ec.Decrypto(dr["DbConnectionStr"].ToString()));
                        conn.SqlTimeOut = timeOut;
                        string runsql = string.Format("call {0} ('{1}','{2}')", dr["CallBackName"], dr["EtlID"], dr["GroupName"]);

                        log.Append("<br/>SQL:");
                        log.Append(runsql);
                        try
                        {
                            conn.ExecuteSql(runsql);
                            //new SysOperationLog(loginInfo).Log("回调存储过程", "", "添加", OperationStatus.成功, log.ToString(), "系统");
                            // TaskDetailsID: null  5:回调存储过程 成功
                            new SysOperationLog(loginInfo).LogETL("", dr["EtlID"].ToString(), DateTime.Now, log.ToString(), 5, "系统");
                        }
                        catch (Exception ex)
                        {
                            log.Append("<br/>" + ex.Message);
                            //new SysOperationLog(loginInfo).Log("回调存储过程", "", "添加", OperationStatus.失败, log.ToString(), "系统");
                            // TaskDetailsID: null  6:回调存储过程 失败
                            new SysOperationLog(loginInfo).LogETL("", dr["EtlID"].ToString(), DateTime.Now, log.ToString(), 6, "系统");
                        }
                    }
                }
                catch (Exception ex)
                {
                    log.Append("<br/>" + ex.Message);
                    //new SysOperationLog(loginInfo).Log("回调存储过程", "", "添加", OperationStatus.失败, log.ToString(), "系统");
                    // TaskDetailsID: null  6:回调存储过程 失败
                    new SysOperationLog(loginInfo).LogETL("", dr["EtlID"].ToString(), DateTime.Now, log.ToString(), 6, "系统");
                }
            }
            else if (dr["CallBackType"].ToString() == "2")//HTTP回调方法
            {
                // 需调整参数 -- 待续 --
                string targetURL = dr["CallBackName"].ToString();
                string callbackGuid = dr["CallBackGuid"].ToString();
                Hashtable param = new Hashtable();
                // 获取 HTTP回调 参数
                ETL_SourceSystemDAL sourceSystemDAL = new ETL_SourceSystemDAL(this.loginInfo);
                var parasList = sourceSystemDAL.GetCallBackParasList(new IDataParameter[] { new SqlParameter("@ParentGUID", callbackGuid) });
                foreach (var paraItem in parasList)
                {
                    param[paraItem.KeyName] = paraItem.KeyValue;
                }
                param["EtlID"] = dr["EtlID"].ToString();
                param["GroupName"] = dr["GroupName"].ToString();

                string meghod = "POST";
                var encoding = Encoding.UTF8;
                var errorMessage = string.Empty;
                var callResult = HttpUtils.PostAndGetHTML(targetURL, param, meghod, encoding, (e) =>
                {
                    // new SysOperationLog(loginInfo).Log("回调 Http", "", "添加", OperationStatus.失败, targetURL + e.Message, "系统");
                    // TaskDetailsID: null  6:回调 Http 失败
                    new SysOperationLog(loginInfo).LogETL("", dr["EtlID"].ToString(), DateTime.Now, log.ToString(), 6, "系统");
                    errorMessage = e.Message;
                });

            }
            #region 原 web服务回调方法 --废弃--
            //else if (dr["CallBackType"].ToString() == "2")//web服务回调方法
            //{
            //    string url = dr["CallBackName"].ToString();
            //    try
            //    {
            //        string[] args = new string[2];
            //        args[0] = dr["EtlID"].ToString();
            //        args[1] = dr["GroupName"].ToString();
            //        object result = WebServiceHelper.InvokeWebService(url, "GetData", args);
            //    }
            //    catch (Exception ex)
            //    {
            //        new SysOperationLog(loginInfo).Log("回调WebService", "", "添加", OperationStatus.失败, url + ex.Message, "系统");
            //    }
            //}
            #endregion

        }
        public void ClearStep(string taskID)
        {
            try
            {
                IDataParameter[] paras = new SqlParameter[1];
                paras[0] = new SqlParameter("@TaskID", taskID);
                HIDB.dbo con = base.conn();
                con.StoredUpdate("Working_db.UP_ETL_Task_ClearStep", paras);
            }
            catch (Exception ex) { throw ex; }
        }
        /// <summary>
        /// 获取ETL_JobInfo对象列表
        /// </summary>
        public List<ETL_TaskInfo> GetList(IDataParameter[] sqlparams = null)
        {
            List<ETL_TaskInfo> list = new List<ETL_TaskInfo>();
            DataTable dt = new DataTable();
            HIDB.dbo con = base.conn();
            dt = con.StoredDataTable("Working_db.UP_ETL_Task_List", sqlparams);

            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    ETL_TaskInfo model = new ETL_TaskInfo();
                    model.TaskID = row["TaskID"].ToString();
                    model.TaskName = row["TaskName"].ToString();
                    model.TaskType = Convert.ToInt32(row["TaskType"]);
                    if (row["TaskType"].ToString() == "0")
                        model.TaskTypeStr = "重复执行";
                    else if (row["TaskType"].ToString() == "1")
                        model.TaskTypeStr = "执行1次后删除";

                    model.FrequencyType = Convert.ToInt32(row["FrequencyType"]);
                    if (string.IsNullOrEmpty(row["_parentId"].ToString()))
                    {
                        if (!string.IsNullOrEmpty(row["TaskStartTime"].ToString()))
                        {
                            string stime = Convert.ToDateTime(row["TaskStartTime"]).ToString("HH:mm");
                            string etime = Convert.ToDateTime(row["TaskEndTime"]).ToString("HH:mm");
                            string strTime = "执行一次：" + stime;
                            if (model.FrequencyType == 1)
                                strTime = string.Format("执行间隔：{0}分<br>{1}-{2}", row["TaskEveryDayIntervalMinute"], stime, etime);
                            model.TaskStartTime = strTime;
                        }
                    }
                    else
                        model.TaskStartTime = "";
                    model.TaskIntervalDaysStr = Convert.ToInt32(row["TaskIntervalDays"]) > 0 ? "每隔" + row["TaskIntervalDays"].ToString() + "天" : "";
                    model.TaskStartDate = row["TaskStartDate"].ToString(); //FormatData(row["TaskStartDate"].ToString(), "yyyy-MM-dd");//ts;
                    model.TaskEndDate = row["TaskEndDate"].ToString();// FormatData(row["TaskEndDate"].ToString(), "yyyy-MM-dd");//te;
                    model.TaskStatus = row["TaskStatus"].ToString() == "" ? int.Parse("0") : int.Parse(row["TaskStatus"].ToString());
                    if (!string.IsNullOrEmpty(model.TaskID))
                    {
                        switch (model.TaskStatus)
                        {
                            case 0:
                                model.TaskStatusStr = "<span class='grid_r'>禁用</span>";
                                break;
                            case 1:
                                model.TaskStatusStr = "启用";
                                break;
                        }
                    }
                    else
                    {
                        switch (model.TaskStatus)
                        {
                            case 1:
                                model.TaskStatusStr = "任务启动";
                                break;
                            case 2:
                                model.TaskStatusStr = "任务成功";
                                break;
                            case 3:
                                model.TaskStatusStr = "任务失败";
                                break;
                        }
                    };
                    model.CreateTime = FormatData(row["CreateTime"].ToString(), "yyyy-MM-dd HH:mm:ss");//row["CreateTime"].ToString() == "" ? "" : Convert.ToDateTime(row["CreateTime"]).ToString("yyyy-MM-dd HH:mm:ss");
                    model._parentId = row["_parentId"].ToString();
                    model.state = row["_parentId"].ToString() == "" ? "closed" : "";
                    model.IsCopy = row["IsCopy"].ToString();

                    if (!string.IsNullOrEmpty(model.TaskID))
                    {
                        string str = string.Format("&nbsp;<a onclick='View(\"{0}\",\"{1}\");' class='grid_a'>查看记录</a> | <a onclick='Edit(\"{0}\");' class='grid_a'>修改</a> | <a onclick='DeleteItem(\"{0}\");'  class='grid_a'>删除</a>", model.TaskID, model.TaskName);
                        if (model.IsCopy == "1")
                            str = "　　　　　　 " + str;
                        else
                            str = "<input type='button' onclick='DoTask(\"" + model.TaskID + "\");' value='立即执行'/> " + str;
                        if (model.TaskStatus == 1)
                            str = str + " | <a onclick='UpStatus(\"" + model.TaskID + "\",0);'  class='grid_a'>禁用</a>";
                        else
                            str = str + " | <a onclick='UpStatus(\"" + model.TaskID + "\",1);'  class='grid_a'>启用</a>";
                        model.TaskDescription = str;
                    }
                    else
                        model.TaskDescription = "";

                    list.Add(model);
                }
            }

            return list;
        }
        public string FormatData(string data, string dataType)
        {
            string te = string.Empty;
            if (data != "")
            {
                DateTime dtcmp = Convert.ToDateTime("2000-1-1");
                DateTime dts = Convert.ToDateTime(data);
                if (dts > dtcmp)
                    te = dts.ToString(dataType);
                return te;
            }
            else
                return "";
        }
        public List<ETL_Task_Details> GetDetails(IDataParameter[] sqlparams = null)
        {
            List<ETL_Task_Details> list = new List<ETL_Task_Details>();
            DataTable dt = new DataTable();
            HIDB.dbo con = base.conn();
            dt = con.StoredDataTable("Working_db.UP_ETL_Task_Details", sqlparams);

            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {

                    ETL_Task_Details model = new ETL_Task_Details()
                    {
                        GUID = row["TaskID"].ToString(),
                        TaskID = row["TaskID"].ToString(),
                        JobStepID = row["JobStepID"].ToString(),
                        StepName = row["StepName"].ToString(),
                        StepType = row["StepType"].ToString(),
                        JobStepOrderID = Convert.ToInt32(row["JobStepOrderID"])
                    };
                    list.Add(model);
                }
            }

            return list;
        }
        public int GetListCount(IDataParameter[] sqlparams)
        {
            DataTable dt = new DataTable();
            HIDB.dbo con = base.conn();
            dt = con.StoredDataTable("Working_db.UP_ETL_Task_ListCount", sqlparams);

            if (dt != null && dt.Rows.Count > 0)
            {
                return Int32.Parse(dt.Rows[0][0].ToString());
            }
            else
                return 0;
        }

        public bool Delete(string id)
        {
            try
            {
                SqlParameter[] sqlparams = { new SqlParameter("@ID", id) };
                //执行操作
                HIDB.dbo con = base.conn();
                con.StoredUpdate("Working_db.UP_ETL_Task_DeleteItem", sqlparams);
                return true;
            }
            catch (Exception ex) { throw ex; }
        }
        public List<ETL_Task_Details_HistoryLog> GetTaskLog(IDataParameter[] sqlparams)
        {
            try
            {
                List<ETL_Task_Details_HistoryLog> list = new List<ETL_Task_Details_HistoryLog>();
                DataTable dt = new DataTable();
                HIDB.dbo con = base.conn();
                dt = con.StoredDataTable("Working_db.UP_ETL_Task_GetTaskLog", sqlparams);

                if (dt != null && dt.Rows.Count > 0)
                {
                    foreach (DataRow row in dt.Rows)
                    {

                        ETL_Task_Details_HistoryLog model = new ETL_Task_Details_HistoryLog();

                        model.GUID = row["GUID"].ToString();
                        model.StepName = row["StepName"].ToString();
                        model.CreateUser = row["CreateUser"].ToString();
                        model.StartTime = Convert.ToDateTime(row["StartTime"]).ToString("yyyy-MM-dd HH:mm:ss");
                        model.EndTime = Convert.ToDateTime(row["EndTime"]).ToString("yyyy-MM-dd HH:mm:ss");
                        model.BatchTime = Convert.ToDateTime(row["BatchTime"]).ToString("yyyy-MM-dd HH:mm:ss");

                        model.RunDuration = row["RunDuration"].ToString() + "秒";
                        model.Result = row["Result"].ToString();
                        model.Count = int.Parse(row["Count"].ToString());
                        string img = string.Empty;
                        string imsg = string.Empty;
                        switch (row["TaskStatus"].ToString())
                        {
                            case "0":
                                img = "stop.jpg";
                                imsg = "任务暂停";
                                break;
                            case "1":
                                img = "start.jpg";
                                imsg = "任务启动";
                                break;
                            case "2":
                                img = "tree_dnd_yes.png";
                                imsg = "任务成功";
                                break;
                            case "3":
                                img = "no.png";
                                imsg = "任务失败";
                                break;
                        }
                        model.TaskStatus = "<img src='/images/" + img + "' alt='" + imsg + "' width='15' />";

                        list.Add(model);
                    }
                }

                return list;
            }
            catch (Exception ex) { throw ex; }

        }

        public DataTable SearchETLTaskLogList(List<SqlParameter> queryParas)
        {
            DataTable dt = new DataTable();
            try
            {
                HIDB.dbo con = base.conn();
                dt = con.StoredDataTable("[Working_db].[UP_ETL_TaskLog_SearchList]", queryParas.ToArray());
            }
            catch (Exception)
            {

                throw;
            }
            return dt;
        }

        public int SearchETLTaskLogCount(IDataParameter[] queryParas)
        {
            int result = 0;
            try
            {
                HIDB.dbo con = base.conn();
                DataTable dt = con.StoredDataTable("[Working_db].[UP_ETL_TaskLog_SearchListCount]", queryParas);
                result = int.Parse(dt.Rows[0][0].ToString());
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }
        public List<ETL_Task_Details_HistoryLog> GetTaskLogItem(string id)
        {
            try
            {
                List<ETL_Task_Details_HistoryLog> list = new List<ETL_Task_Details_HistoryLog>();
                DataTable dt = new DataTable();
                SqlParameter[] sqlparams = { 
                    new SqlParameter("@ID",id )
                  };
                HIDB.dbo con = base.conn();
                dt = con.StoredDataTable("Working_db.UP_ETL_Task_GetTaskLogInfo", sqlparams);

                if (dt != null && dt.Rows.Count > 0)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        string s = row["TaskStatus"].ToString();
                        switch (s)
                        {
                            case "1":
                                s = "任务启动";
                                break;
                            case "2":
                                s = "任务成功";
                                break;
                            case "3":
                                s = "任务失败";
                                break;
                        }
                        ETL_Task_Details_HistoryLog model = new ETL_Task_Details_HistoryLog()
                        {
                            StepName = row["StepName"].ToString(),
                            CreateUser = row["CreateUser"].ToString(),
                            StartTime = Convert.ToDateTime(row["StartTime"]).ToString("yyyy-MM-dd HH:mm:ss"),
                            EndTime = Convert.ToDateTime(row["EndTime"]).ToString("yyyy-MM-dd HH:mm:ss"),
                            TaskStatus = s,
                            RunDuration = row["RunDuration"].ToString() + "秒",
                            Result = row["Result"].ToString(),
                            BatchTime = Convert.ToDateTime(row["BatchTime"]).ToString("yyyy-MM-dd HH:mm:ss")
                        };
                        list.Add(model);
                    }
                }

                return list;
            }
            catch (Exception ex) { throw ex; }
        }
        public DataTable TaskRunStatus(string taskName, int rows, int page)
        {
            try
            {
                SqlParameter[] queryParas = new SqlParameter[]{
                new SqlParameter("@TaskName",taskName==null ?"":taskName),
                new SqlParameter("@Rows",rows),
                new SqlParameter("@PageNum",page),
                };
                HIDB.dbo con = base.conn();
                return con.StoredDataTable("[Working_db].[UP_ETL_Task_GetTaskStatus]", queryParas);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable GetTableColumns()
        {
            try
            {
                HIDB.dbo con = base.conn();
                return con.GetDataTable("select * from working_db.GetFiveMinuteTaskStatus where 1<>1");
            }
            catch (Exception)
            {
                throw;
            }
        }

        #region ETL_Task

        public List<ETL_TaskInfo> GetTaskList(string taskName, int taskType, int taskStatus
            , string orderBy, string asc
            , int pageIndex, int pageSize, out int recordCount)
        {
            recordCount = 0;

            List<ETL_TaskInfo> result = new List<ETL_TaskInfo>();
            try
            {
                HIDB.dbo cmd = base.conn();
                List<string> sqlWhere = new List<string>();
                List<SqlParameter> cmdParms = new List<SqlParameter>();
                sqlWhere.Add("where 1=1");
                if (!string.IsNullOrEmpty(taskName))
                {
                    sqlWhere.Add("TaskName like '%'+@TaskName+'%'");
                    cmdParms.Add(new SqlParameter("@TaskName", taskName));
                }
                if (taskType > -1)
                {
                    sqlWhere.Add("TaskType=@TaskType");
                    cmdParms.Add(new SqlParameter("@TaskType", taskType));
                }
                if (taskStatus > -1)
                {
                    sqlWhere.Add("TaskStatus=@TaskStatus");
                    cmdParms.Add(new SqlParameter("@TaskStatus", taskStatus));
                }
                string strSql = @"SELECT *,(select count(*) FROM [Working_db].[ETL_Task] {0}) as Count FROM [Working_db].[ETL_Task] {0}";

                strSql += string.Format(" order by {0} {1} offset @Rows * (@PageNum-1) rows fetch next @Rows rows only", orderBy, asc);
                strSql = string.Format(strSql, string.Join(" and ", sqlWhere.ToArray()));
                cmdParms.Add(new SqlParameter("@Rows", pageSize));
                cmdParms.Add(new SqlParameter("@PageNum", pageIndex));
                DataTable dt = cmd.GetDataTable(strSql, cmdParms.ToArray());
                result = dt.MapToList<ETL_TaskInfo>();
                if (result.Count > 0)
                    recordCount = result[0].Count;

            }
            catch (Exception ex)
            {
                Logger.LogError("[Working_db].[ETL_Task]", "GetTaskList", 0, 1, ex, "", "");
            }
            return result;
        }

        public List<ETL_TaskInfo> GetTaskList(string taskName, string taskType, string taskStatus
    , string orderBy, string orderType
    , int pageIndex, int pageSize, out int recordCount)
        {
            recordCount = 0;

            List<ETL_TaskInfo> result = new List<ETL_TaskInfo>();
            try
            {
                HIDB.dbo cmd = base.conn();
                string strSql = "Working_db.UP_ETL_Task_List";


                List<SqlParameter> cmdParms = new List<SqlParameter>() { 
                    new SqlParameter("@TaskName", taskName),
                    new SqlParameter("@TaskType", taskType),
                    new SqlParameter("@TaskStatus", taskStatus),
                    new SqlParameter("@Sort", orderBy),
                    new SqlParameter("@Order", orderType),
                    new SqlParameter("@Rows", pageSize),
                    new SqlParameter("@PageNum", pageIndex)
                };
                DataTable dt = cmd.StoredDataTable(strSql, cmdParms.ToArray());
                result = dt.MapToList<ETL_TaskInfo>();
                recordCount = result.Count;

                dt = cmd.StoredDataTable("Working_db.UP_ETL_Task_ListCount", cmdParms.ToArray());
                if (dt != null && dt.Rows.Count > 0)
                {
                    recordCount = Int32.Parse(dt.Rows[0][0].ToString());
                }

            }
            catch (Exception ex)
            {
                Logger.LogError("[Working_db].[ETL_Task]", "GetTaskList", 0, 1, ex, "", "");
            }
            return result;
        }

        public ETL_TaskInfo GetTaskInfoById(string taskID)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"SELECT * FROM [Working_db].[ETL_Task] WHERE TaskID=@TaskID";
                IDataParameter[] cmdParms = { new SqlParameter("@TaskID", taskID) };
                DataTable dts = cmd.GetDataTable(strSql, cmdParms);
                var list = dts.MapToList<ETL_TaskInfo>();
                if (list != null && list.Count > 0)
                {
                    ETL_TaskInfo info = list[0];
                    switch (info.TaskPlanType)
                    {
                        case 1:
                            strSql = @"select * from [Working_db].[ETL_Task_PlanDay] where TaskGuid=@TaskID";
                            dts = cmd.GetDataTable(strSql, cmdParms);
                            if (dts.Rows.Count > 0)
                            {
                                info.TaskIntervalDays = Convert.ToInt32(dts.Rows[0]["TaskIntervalDays"].ToString());
                                info.TaskEveryDayIntervalMinute = Convert.ToInt32(dts.Rows[0]["TaskEveryDayIntervalMinute"].ToString());
                            }
                            break;
                        case 2:
                            strSql = @"select * from [Working_db].[ETL_Task_PlanWeek] where TaskGuid=@TaskID";
                            dts = cmd.GetDataTable(strSql, cmdParms);
                            if (dts.Rows.Count > 0)
                            {
                                info.TaskIntervalWeeks = dts.Rows[0]["TaskIntervalWeeks"].ToString();
                                List<string> wd = new List<string>();
                                foreach (DataRow dr in dts.Rows)
                                {
                                    wd.Add(dr["WeekDay"].ToString());
                                }
                                info.WeekDay = string.Join(",", wd.ToArray());
                            }
                            break;
                        case 3:
                            strSql = @"select * from [Working_db].[ETL_Task_PlanMonth] where TaskGuid=@TaskID";
                            dts = cmd.GetDataTable(strSql, cmdParms);
                            if (dts.Rows.Count > 0)
                            {
                                info.PlanType = dts.Rows[0]["PlanType"].ToString();
                                info.TaskIntervalMonth = dts.Rows[0]["TaskIntervalMonth"].ToString();
                                info.DayForMonth = dts.Rows[0]["DayForMonth"].ToString();
                                info.WeekForMonth = dts.Rows[0]["WeekForMonth"].ToString();
                                info.WeekDay = dts.Rows[0]["WeekDay"].ToString();
                            }
                            break;
                    }

                    return info;
                }
                else
                    return new ETL_TaskInfo();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<ETL_TaskInfo> GetTaskByTaskNameWithOutID(string taskID, string taskName)
        {
            List<ETL_TaskInfo> result = new List<ETL_TaskInfo>();
            try
            {
                string strSql = "SELECT * FROM [Working_db].[ETL_Task] WHERE 1=1";
                List<IDataParameter> cmdParms = new List<IDataParameter>();

                if (!string.IsNullOrEmpty(taskName))
                {
                    strSql += " AND TaskName =@TaskName";
                    cmdParms.Add(new SqlParameter("@TaskName", taskName));
                }
                if (!string.IsNullOrEmpty(taskID))
                {
                    strSql += " AND TaskID!=@TaskID";
                    cmdParms.Add(new SqlParameter("@TaskID", taskID));
                }
                HIDB.dbo cmd = base.conn();
                DataTable dt = cmd.GetDataTable(strSql, cmdParms.ToArray());
                result = dt.MapToList<ETL_TaskInfo>();
            }
            catch (Exception ex)
            {
                Logger.LogError("[Working_db].[ETL_Task]", "GetTaskByTaskNameWithOutID", 0, 1, ex, "", "");
            }
            return result;

        }

        public int SaveTaskPlan(ETL_TaskInfo TaskPlanModel)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = "";
                switch (TaskPlanModel.TaskPlanType)
                {
                    case 1:
                        strSql = @"IF NOT EXISTS(SELECT 1 FROM [Working_db].[ETL_Task_PlanDay] WHERE TaskGuid=@TaskGuid)
BEGIN
insert into [Working_db].[ETL_Task_PlanDay]
select @TaskGuid,@TaskIntervalDays,@TaskEveryDayIntervalMinute
ENd
else
BEGIN
update [Working_db].[ETL_Task_PlanDay] set TaskIntervalDays=@TaskIntervalDays,TaskEveryDayIntervalMinute=@TaskEveryDayIntervalMinute  WHERE TaskGuid=@TaskGuid
ENd";
                        SqlParameter[] cmdParms = new SqlParameter[]
                        {
                              new SqlParameter("@TaskGuid",TaskPlanModel.TaskGuid)
                             ,new SqlParameter("@TaskIntervalDays",TaskPlanModel.TaskIntervalDays)
                             ,new SqlParameter("@TaskEveryDayIntervalMinute",TaskPlanModel.TaskEveryDayIntervalMinute) 
                        };
                        cmd.ExecuteSql(strSql, cmdParms);
                        break;
                    case 2:
                        strSql = @"delete from [Working_db].[ETL_Task_PlanWeek] where TaskGuid=@TaskGuid
insert into [Working_db].[ETL_Task_PlanWeek]
select @TaskGuid,@TaskIntervalWeeks,a.name from [dbo].[SPLIT](@WeekDay,',') a";
                        SqlParameter[] cmdParms2 = new SqlParameter[]
                        {
                              new SqlParameter("@TaskGuid",TaskPlanModel.TaskGuid)
                             ,new SqlParameter("@TaskIntervalWeeks",TaskPlanModel.TaskIntervalWeeks)
                             ,new SqlParameter("@WeekDay",TaskPlanModel.WeekDay) 
                        };
                        cmd.ExecuteSql(strSql, cmdParms2);
                        break;
                    case 3:
                        strSql = @"IF NOT EXISTS(SELECT 1 FROM [Working_db].[ETL_Task_PlanMonth] WHERE TaskGuid=@TaskGuid)
BEGIN
insert into [Working_db].[ETL_Task_PlanMonth]
select @TaskGuid,@PlanType,@TaskIntervalMonth,@DayForMonth,@WeekForMonth,@WeekDay
ENd
else
BEGIN
update [Working_db].[ETL_Task_PlanMonth] set PlanType=@PlanType,TaskIntervalMonth=@TaskIntervalMonth,DayForMonth=@DayForMonth,WeekForMonth=@WeekForMonth,WeekDay=@WeekDay  WHERE TaskGuid=@TaskGuid
ENd";
                        SqlParameter[] cmdParms3 = new SqlParameter[]
                        {
                              new SqlParameter("@TaskGuid",TaskPlanModel.TaskGuid)
                             ,new SqlParameter("@PlanType",TaskPlanModel.PlanType)
                             ,new SqlParameter("@TaskIntervalMonth",TaskPlanModel.TaskIntervalMonth) 
                             ,new SqlParameter("@DayForMonth",TaskPlanModel.DayForMonth) 
                             ,new SqlParameter("@WeekForMonth",TaskPlanModel.WeekForMonth) 
                             ,new SqlParameter("@WeekDay",TaskPlanModel.WeekDay)  
                        };
                        cmd.ExecuteSql(strSql, cmdParms3);
                        break;
                }
                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int Insert2(ETL_TaskInfo taskInfo)
        {
            int num = -1;
            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            //执行命令返回结果
            try
            {
                string strSql = @"IF NOT EXISTS(SELECT 1 FROM [Working_db].[ETL_Task] WHERE TaskID=@TaskID)
BEGIN
	INSERT INTO [Working_db].[ETL_Task]
	(TaskID,TaskName,TaskType,TaskStatus
	,UsedMailReport,UsedSMSReport,UsedRTXReport,TaskIntervalDays
	,FrequencyType,TaskStartTime,TaskEndTime,TaskEveryDayIntervalMinute
	,TaskStartDate,TaskEndDate,TaskDescription,TaskPlanType,RetryTimes,CreateTime,WaitTimes) 
	VALUES
	(@TaskID,@TaskName,@TaskType,@TaskStatus
	,@UsedMailReport,@UsedSMSReport,@UsedRTXReport,@TaskIntervalDays
	,@FrequencyType,@TaskStartTime,@TaskEndTime,@TaskEveryDayIntervalMinute
	,@TaskStartDate,@TaskEndDate,@TaskDescription,@TaskPlanType,@RetryTimes,GETDATE(),@WaitTimes);
declare @type int
declare @interval int=@TaskIntervalDays
declare @subday_interval int 
declare @subday_type int  
declare @start_time varchar(10) 
if(@TaskType=1)
begin
select @type=1
end
if(@TaskPlanType=1)
begin
select @type=4
end
else if(@TaskPlanType=2)
begin
select @type=8
select @interval=@WeekDay
end
else if(@TaskPlanType=3)
begin
select @type=16
end
if(@FrequencyType=0)
begin
select @subday_type=0x1
end
else 
begin
select @subday_type=0x4
end
select @start_time=replace(CONVERT(varchar(100),CONVERT(datetime,@TaskStartTime),108),':','')
exec msdb.dbo.sp_add_schedule 
@schedule_name=@TaskID --名称
,@enabled=1--状态：启用
,@freq_type=@type --1一次,4每天，8每周，16每月
,@freq_interval=@interval --每X天
,@freq_subday_type=@subday_type --间隔分钟
,@freq_subday_interval=@TaskEveryDayIntervalMinute --间隔X分钟
,@freq_recurrence_factor= @TaskIntervalDays--  相隔的周数或月数
,@active_start_time =@start_time --开始执行作业的时间
 
exec working_db.CreateSysJob

END";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@TaskID",taskInfo.TaskID)
                     ,new SqlParameter("@TaskName",taskInfo.TaskName??string.Empty)
                     ,new SqlParameter("@TaskType",taskInfo.TaskType)
                     ,new SqlParameter("@TaskStatus",taskInfo.TaskStatus)
                     ,new SqlParameter("@UsedMailReport",taskInfo.UsedMailReport)
                     ,new SqlParameter("@UsedSMSReport",taskInfo.UsedSMSReport)
                     ,new SqlParameter("@UsedRTXReport",taskInfo.UsedRTXReport)
                     ,new SqlParameter("@TaskIntervalDays",taskInfo.TaskIntervalDays)
                     ,new SqlParameter("@FrequencyType",taskInfo.FrequencyType)
                     ,new SqlParameter("@TaskStartTime",taskInfo.TaskStartTime??string.Empty)
                     ,new SqlParameter("@TaskEndTime",taskInfo.TaskEndTime??string.Empty)
                     ,new SqlParameter("@TaskEveryDayIntervalMinute",taskInfo.TaskEveryDayIntervalMinute)
                     ,new SqlParameter("@TaskStartDate",taskInfo.TaskStartDate)
                     ,new SqlParameter("@TaskEndDate",taskInfo.TaskEndDate)
                     ,new SqlParameter("@TaskDescription",taskInfo.TaskDescription??string.Empty)
                     ,new SqlParameter("@WeekDay",taskInfo.WeekDay??string.Empty)
                     ,new SqlParameter("@TaskPlanType",taskInfo.TaskPlanType)
                     ,new SqlParameter("@RetryTimes",taskInfo.RetryTimes)
                     ,new SqlParameter("@WaitTimes",taskInfo.WaitTimes??string.Empty)
                     ,new SqlParameter("@DBName",taskInfo.DBName+"_ETL")
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_TargetSystem_Insert", "Insert2", 0, 1, ex, "", "");
            }
            return num;
        }
        public int Update2(ETL_TaskInfo taskInfo)
        {
            int num = -1;

            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"IF EXISTS(SELECT 1 FROM [Working_db].[ETL_Task] WHERE TaskID=@TaskID)
BEGIN
	UPDATE [Working_db].[ETL_Task] 
	SET TaskName=@TaskName,
	TaskType=@TaskType,
	TaskStatus=@TaskStatus,
	TaskEveryDayIntervalMinute=@TaskEveryDayIntervalMinute,
	UsedMailReport=@UsedMailReport,
	UsedSMSReport=@UsedSMSReport,
	UsedRTXReport=@UsedRTXReport,  
	FrequencyType=@FrequencyType,
	TaskStartTime=@TaskStartTime,
	TaskEndTime=@TaskEndTime, 
	TaskStartDate=@TaskStartDate,
	TaskEndDate=@TaskEndDate ,
	TaskDescription=@TaskDescription ,
	TaskIntervalDays=@TaskIntervalDays ,
	TaskPlanType=@TaskPlanType ,
	RetryTimes=@RetryTimes ,
	WaitTimes=@WaitTimes 
	WHERE TaskID=@TaskID;
declare @type int
declare @interval int=@TaskIntervalDays
declare @subday_interval int 
declare @subday_type int 
declare @start_time varchar(10) 
if(@TaskType=1)
begin
select @type=1
end
if(@TaskPlanType=1)
begin
select @type=4
end
else if(@TaskPlanType=2)
begin
select @type=8
select @interval=@WeekDay
end
else if(@TaskPlanType=3)
begin
select @type=16
select @interval=@WeekDay
end

if(@FrequencyType=0)
begin
select @subday_type=0x1
end
else 
begin
select @subday_type=0x4
end
select @start_time=replace(CONVERT(varchar(100),CONVERT(datetime,@TaskStartTime),108),':','')
if exists (select 1 from msdb.dbo.sysschedules a where a.name=@TaskID)
begin
	exec msdb.dbo.sp_update_schedule 
	@name=@TaskID--名称
	,@new_name=@TaskID --新名称
	,@enabled=1--状态：启用
	,@freq_type=@type --每天
	,@freq_interval=@interval --每X天
	,@freq_subday_type=@subday_type--间隔分钟
    ,@freq_subday_interval=@TaskEveryDayIntervalMinute --间隔X分钟
    ,@freq_recurrence_factor= @TaskIntervalDays--  相隔的周数或月数
    ,@active_start_time =@start_time --开始执行作业的时间 
end
else
begin
    exec msdb.dbo.sp_add_schedule 
    @schedule_name=@TaskID --名称
    ,@enabled=1--状态：启用
    ,@freq_type=@type --1一次,4每天，8每周，16每月
    ,@freq_interval=@interval --每X天
    ,@freq_subday_type=@subday_type--间隔分钟
    ,@freq_subday_interval=@TaskEveryDayIntervalMinute --间隔X分钟
    ,@freq_recurrence_factor= @TaskIntervalDays--  相隔的周数或月数
    ,@active_start_time =@start_time --开始执行作业的时间 
end 
exec working_db.CreateSysJob
END";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@TaskID",taskInfo.TaskID)
                     ,new SqlParameter("@TaskName",taskInfo.TaskName??string.Empty)
                     ,new SqlParameter("@TaskType",taskInfo.TaskType)
                     ,new SqlParameter("@TaskStatus",taskInfo.TaskStatus)
                     ,new SqlParameter("@TaskEveryDayIntervalMinute",taskInfo.TaskEveryDayIntervalMinute)
                     ,new SqlParameter("@UsedMailReport",taskInfo.UsedMailReport)
                     ,new SqlParameter("@UsedSMSReport",taskInfo.UsedSMSReport)
                     ,new SqlParameter("@UsedRTXReport",taskInfo.UsedRTXReport) 
                     ,new SqlParameter("@FrequencyType",taskInfo.FrequencyType)
                     ,new SqlParameter("@TaskStartTime",Convert.ToDateTime(taskInfo.TaskStartTime).ToShortTimeString())
                     ,new SqlParameter("@TaskEndTime", Convert.ToDateTime(taskInfo.TaskEndTime).ToShortTimeString())
                     ,new SqlParameter("@TaskStartDate",taskInfo.TaskStartDate)
                     ,new SqlParameter("@TaskEndDate",taskInfo.TaskEndDate)
                     ,new SqlParameter("@TaskDescription",taskInfo.TaskDescription??string.Empty)
                     ,new SqlParameter("@WeekDay",taskInfo.WeekDay??string.Empty)
                     ,new SqlParameter("@TaskIntervalDays",taskInfo.TaskIntervalDays)
                     ,new SqlParameter("@TaskPlanType",taskInfo.TaskPlanType)
                     ,new SqlParameter("@RetryTimes",taskInfo.RetryTimes)
                     ,new SqlParameter("@WaitTimes",taskInfo.WaitTimes??string.Empty)
                     ,new SqlParameter("@DBName",taskInfo.DBName+"_ETL")
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                Logger.LogError("[Working_db].[ETL_Task]", "Update2", 0, 1, ex, "", "");
            }
            return num;
        }

        public int UpdateStatus(ETL_TaskInfo taskInfo)
        {
            int num = -1;

            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"IF EXISTS(SELECT 1 FROM [Working_db].[ETL_Task] WHERE TaskID=@TaskID)
BEGIN
	UPDATE [Working_db].[ETL_Task] SET TaskStatus=@TaskStatus WHERE TaskID=@TaskID;
if(@DBType='2')
begin
    exec working_db.CreateSysJob
    exec working_db.EnableSysJob @TaskID,@TaskStatus
end
END";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@TaskID",taskInfo.TaskID) 
                     ,new SqlParameter("@TaskStatus",taskInfo.TaskStatus) 
                     ,new SqlParameter("@DBType",taskInfo.DBType??"1") 
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return num;
        }
        public int Delete2(string taskID, string DBType)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"DELETE FROM [Working_db].[ETL_Task] WHERE TaskID=@TaskID;
DELETE FROM [Working_db].[ETL_Task_Details] WHERE TaskID=@TaskID;
if(@DBType='2')
begin
exec working_db.DelSysJob @TaskID;
end
declare @sql nvarchar(max) = '' 
select @sql='exec msdb.dbo.sp_delete_schedule '+cast(a.schedule_id as nvarchar(100)) 
from msdb.dbo.sysschedules a where a.name=@TaskID

if (@sql<>'')
begin
exec(@sql)
end
";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@TaskID",taskID),
                    new SqlParameter("@DBType",DBType)
                };
                cmd.ExecuteSql(strSql, cmdParms);
                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region ETL_Task_Details

        public int DeleteDetails(string taskID)
        {
            int num = -1;
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"DELETE FROM [Working_db].[ETL_Task_Details] WHERE TaskID=@TaskID";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@TaskID",taskID)
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                Logger.LogError("[Working_db].[ETL_Task]", "Delete2", 0, 1, ex, "", "");
            }
            return num;
        }

        public int InsertDetails(ETL_Task_Details taskDetailInfo)
        {
            int num = -1;
            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            //执行命令返回结果
            try
            {
                string strSql = @"IF NOT EXISTS(SELECT 1 FROM [Working_db].[ETL_Task_Details] WHERE TaskID=@TaskID 
	AND JobStepID=@JobStepID AND StepType=@StepType)
BEGIN 
	INSERT INTO [Working_db].[ETL_Task_Details](GUID,TaskID,JobStepID,JobStepOrderID,StepType) 
	VALUES(@GUID,@TaskID,@JobStepID,@StepOrder,@StepType)
END";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@GUID",taskDetailInfo.GUID)
                     ,new SqlParameter("@TaskID",taskDetailInfo.TaskID)
                     ,new SqlParameter("@JobStepID",taskDetailInfo.JobStepID)
                     ,new SqlParameter("@StepOrder",taskDetailInfo.JobStepOrderID)
                     ,new SqlParameter("@StepType",taskDetailInfo.StepType)
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                Logger.LogError("[Working_db].[ETL_Task_Details]", "InsertDetails", 0, 1, ex, "", "");
            }
            return num;
        }

        public List<TaskDetailModel> GetTaskDetailsByTaskID(string taskID, string stepType, int DBType)
        {
            List<TaskDetailModel> result = new List<TaskDetailModel>();
            try
            {
                HIDB.dbo cmd = base.conn();
                string strSql = "";

                if (stepType == "L")//推送组
                {
                    //if (DBType == 1)
                    strSql = @"SELECT g.ID as GUID,g.GroupName as Name,g.Description ,s.TargetSysName
FROM [Working_db].[ETL_Task_Details] t inner join [Working_db].SysViewGroup g 
on t.JobStepID=g.ID inner join [Working_db].ETL_TargetSystem s on
g.SysID=s.GUID  where t.StepType='L' and t.TaskID=@TaskID order by t.JobStepOrderID";
                    //                    else//海量实例
                    //                        strSql = @"SELECT m.GUID,m.MDP_ViewName as Name,m.PushSchema+'.'+m.PushName as Description 
                    //FROM [Working_db].[ETL_Task_Details] t inner join [Working_db].[ETL_Target_ViewConfig] m on t.JobStepID=m.GUID where StepType='L' and t.TaskID=@TaskID order by t.JobStepOrderID";
                }
                else
                {
                    if (DBType == 1)
                        strSql = @"select a.guid,a.Name,a.Description,dbo.JoinStr(a.DisplayName,',') as vwName from
(
SELECT top 10000000 m.GUID,m.TableName as Name,m.TableDescription as Description,svc.DisplayName,max(t.JobStepOrderID) as jobstepid
FROM [Working_db].[ETL_Task_Details] t inner join [Working_db].Model_Config m on 
t.JobStepID=m.GUID
inner join Working_db.Model_ColumnConfig mc on m.guid=mc.ModelTableID
inner join (select distinct x.ModelColumnID,x.FactTableName,x.FactSchemaName from Working_db.Model_Column_SourceConfig x) mcs on mc.guid=mcs.ModelColumnID
inner join Working_db.ODS_Config oc on right(mcs.FactTableName,36)=oc.GUID and mcs.FactSchemaName='FACT'
inner join Working_db.ODS_STG_Config osc on oc.guid=osc.ODS_ID
inner join Working_db.ETL_Source_ViewConfig svc on osc.STG_TB_Name=svc.MDP_ViewName and osc.STG_TB_Schema=svc.MDP_ViewSchema
 where StepType='T' and t.TaskID=@TaskID
 group by m.GUID,m.TableName,m.TableDescription,svc.DisplayName
 ) a
 group by a.guid,a.Name,a.Description
 order by max(a.jobstepid)";
                    else//海量实例
                        strSql = @"SELECT m.GUID,m.DisplayName as Name,m.ViewChnName as Description 
FROM [Working_db].[ETL_Task_Details] t inner join [Working_db].ETL_Source_ViewConfig m on t.JobStepID=m.GUID where StepType='T' and t.TaskID=@TaskID order by t.JobStepOrderID";

                }

                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@TaskID",taskID) 
                };
                DataTable dt = cmd.GetDataTable(strSql, cmdParms);
                result = dt.MapToList<TaskDetailModel>();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        #endregion

        #region ETL_Task_Details_HistoryLog

        public List<ETL_Task_Details_HistoryLog> GetTaskLogByTaskID(string taskID, string taskType, string taskStatus, int rows, int page, string DBType)
        {
            List<ETL_Task_Details_HistoryLog> result = new List<ETL_Task_Details_HistoryLog>();
            try
            {
                HIDB.dbo cmd = base.conn();
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@ID",taskID),
                    new SqlParameter("@TaskType",taskType),
                    new SqlParameter("@TaskStatus",taskStatus),
                    new SqlParameter("@rows",rows),
                    new SqlParameter("@page",page),
                    new SqlParameter("@DBType",DBType)
                };
                DataTable dt = cmd.StoredDataTable("Working_db.UP_ETL_Task_GetTaskLog", cmdParms);
                if (DBType == "2")
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        string run_time = dr["run_time"].ToString();
                        StringBuilder sb = new StringBuilder();
                        for (int i = 0; i < run_time.Length; i++)
                        {
                            sb.Append(run_time.Substring(i, 1));
                            if (i == 1 || i == 3)
                            {
                                sb.Append(":");
                            }
                        }
                        dr["StartTime"] = dr["BatchTime"] = string.Format("{0} {1}", dr["StartTime"], sb.ToString());
                        if (dr["TaskStatus"].ToString() == "0")
                            dr["TaskStatus"] = 3;
                        else if (dr["TaskStatus"].ToString() == "1")
                            dr["TaskStatus"] = 2;
                        else
                            dr["TaskStatus"] = 1;
                    }
                }
                result = dt.MapToList<ETL_Task_Details_HistoryLog>();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public List<ETL_Task_Details_HistoryLog> GetAllTaskLog(string taskName, string taskType, string taskStatus, int rows, int page)
        {
            List<ETL_Task_Details_HistoryLog> result = new List<ETL_Task_Details_HistoryLog>();
            try
            {
                HIDB.dbo cmd = base.conn();
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@TaskName",taskName),
                    new SqlParameter("@TaskType",taskType),
                    new SqlParameter("@TaskStatus",taskStatus),
                    new SqlParameter("@rows",rows),
                    new SqlParameter("@page",page)
                };
                DataTable dt = cmd.StoredDataTable("Working_db.UP_ETL_Task_GetAllTaskLog", cmdParms);
                result = dt.MapToList<ETL_Task_Details_HistoryLog>();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        public int CheckDBJob(string dbName)
        {
            try
            {
                HIDB.dbo cmd = base.MSDB();
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@dbName",dbName) 
                };
                string sql = @"select distinct e.DBName,e.DisplayName,a.enabled,e.CreateTime
from sysjobs a 
inner join sysjobschedules c on a.job_id=c.job_id 
inner join MDM_Platform.Working_db.DBDeploy e on left(a.name,32)=e.DBName
where e.DBName=@dbName";
                DataTable dt = cmd.GetDataTable(sql, cmdParms);
                if (dt != null && dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (dr["enabled"].ToString() != "1")
                            return int.Parse(dr["enabled"].ToString());
                    }
                    return 1;
                }
                else
                    return -1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable GetTaskHistoryLogByID(string historyID)
        {
            DataTable result = null;
            try
            {
                HIDB.dbo cmd = base.conn();
                string strSql = @"SELECT a.[TaskID],isnull(b.TaskName,'无') TaskName,a.StepName,CreateUser,a.StartTime,a.EndTime
	 ,case when year(StartTime)=1900 or year(EndTime)=1900 then 0 else datediff(s,StartTime,EndTime) end RunDuration
	 ,a.TaskStatus,Result,a.BatchTime,a.TaskList_GUID as TaskListGUID
 from [Working_db].[ETL_Task_List_History] a
	 left join [Working_db].ETL_Task b
	 on a.[TaskID] =b.[TaskID]
	 Where a.GUID=@GUID";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@GUID",historyID)
                };
                result = cmd.GetDataTable(strSql, cmdParms);
                //DataTable dt = cmd.StoredDataTable(strSql, cmdParms);
                //result = dt.MapToList<ETL_Task_Details_HistoryLog>();
            }
            catch (Exception ex)
            {
                Logger.LogError("[Working_db].[UP_ETL_Task_GetTaskLog]", "GetTaskLogByTaskID", 0, 1, ex, "", "");
            }
            return result;
        }

        #endregion

        #region ETL_Task_List

        #endregion
    }
}